﻿Imports QL_GioiThieu_NhaThue.sqlDataProvider
Imports System.Data.SqlClient

Public Class TaiKhoanDAO
    Public Shared Function GetAll() As DataTable
        Dim listTaiKhoan As New DataTable()
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "select_phanquyen"
            cmd.Connection = cn

            Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
            da.Fill(listTaiKhoan)
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

        Return listTaiKhoan
    End Function

    Public Shared Function GetByID(ByVal paraUID As SqlParameter) As List(Of TaiKhoanDTO)
        Dim listTaiKhoan As New List(Of TaiKhoanDTO)
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_SelectTaikhoan"
            cmd.Connection = cn
            cmd.Parameters.Add(paraUID)
            cn.Open()
            Dim rd As SqlDataReader = cmd.ExecuteReader()
            While rd.Read()
                Dim taikhoan As TaiKhoanDTO = New TaiKhoanDTO()
                taikhoan.strUsername = rd.GetString(0)
                taikhoan.strPassword = rd.GetString(1)
                taikhoan.iMaLoaiTK = rd.GetInt32(2)

                listTaiKhoan.Add(taikhoan)
            End While
            cn.Close()


        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try
        Return listTaiKhoan

    End Function

    Public Shared Sub Insert(ByVal sqlParameters As SqlParameter())
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_InsertTaikhoan"
            cmd.Connection = cn

            cmd.Parameters.AddRange(sqlParameters)
            cn.Open()
            cmd.ExecuteNonQuery()
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try
        
    End Sub

    Public Shared Sub Delete(ByVal paraID As SqlParameter)
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_DeleteTaikhoan"
            cmd.Connection = cn

            cmd.Parameters.Add(paraID)
            cn.Open()
            cmd.ExecuteNonQuery()
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try
        
    End Sub

    Public Shared Sub Update(ByVal sqlParameters As SqlParameter())
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_UpdateTaikhoan"
            cmd.Connection = cn

            cmd.Parameters.AddRange(sqlParameters)
            cn.Open()
            cmd.ExecuteNonQuery()
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try
        
    End Sub
    Public Shared Function TimKiemTKTheoLoaiTK(ByVal paraMaLoaiTK As SqlParameter) As DataTable
        Dim listTaiKhoan As New DataTable()
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_TimKiemTKTheoLoaiTK"
            cmd.Connection = cn
            cmd.Parameters.Add(paraMaLoaiTK)

            Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
            da.Fill(listTaiKhoan)
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

        Return listTaiKhoan
    End Function
End Class
